--select * from common..t_ItemMaster where ItemCode= 'FSJKDFH'
--common..p_UpdItem 'FSJKDFH','TEAK','AA','BLR','JOJU',10.0,89.0,8.0,1780.0,10.0,99.0,0.0,'SUNNY','06/22/2011',19224.0,'CMV'
use common
go
IF OBJECT_ID('dbo.p_UpdItem') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_UpdItem
    IF OBJECT_ID('dbo.p_UpdItem') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_UpdItem >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_UpdItem >>>'
END
go
create proc p_UpdItem
(
	@ItemCode			varchar(50),	-- ItemCode
	@ItemName			varchar(30),	-- ItemName
	@ItemClass			varchar(10),	-- ItemClass
	@Location			varchar(30),	-- Location
	@LotNumber			varchar(30),	-- LotNumber
	@Length				decimal(10,3),	-- Length
	@Width				decimal(10,3),	-- Width
	@Measurement		decimal(10,3),	-- Measurement
	@Quantity			decimal(10,3),	-- Quantity
	@Units				decimal(10,3),	-- Units
	@PurchasePrice		decimal(10,3),	-- Autowrap participant
	@SalesPrice			decimal(10,3),	-- PurchasePrice
	@ProfitMargin		decimal(10,3),	-- ProfitMargin
	@PurchasedFrom		varchar(50),	-- PurchasedFrom
 	@PurchasedDate		datetime ,	-- PurchasedDate
	@Tax				decimal(10,3),	-- Tax
	@CompanyName		varchar(50)		-- CompanyName  data belongs to which CMP
) as
begin
	-- declare

    declare @DateUpdated	datetime	-- datetime when this row was updated
    declare @UserUpdated	varchar(15)	-- user name that updated this row
	declare @Msg 		varchar(255)	-- last error message
	declare @ErrVal		int		-- last error number
	declare @ItemId		int	
	-- initialize
	select @ErrVal=0
	select @DateUpdated=getdate()
	select @UserUpdated=suser_name()
	select @ItemId=0
	begin tran p_UpdItem
		select @ErrVal=0
		if @ErrVal=0
		begin
  print 'Update started'
				update  common..t_ItemMaster 
				set ItemName =@ItemName,
				ItemClass=@ItemClass,
				Location =@Location,
				LotNumber = @LotNumber	,
				Length = @Length,
				Width =@Width,
				Measurement=@Measurement,
				Quantity =@Quantity,
				Units =@Units,
				PurchasePrice =@PurchasePrice,
				SalesPrice=@SalesPrice,
				ProfitMargin=@ProfitMargin,
				PurchasedFrom=@PurchasedFrom,
				PurchasedDate=convert(date,@PurchasedDate),
				Tax=@Tax,
				CompanyName=@CompanyName
				where  ItemCode = @ItemCode
				select @ErrVal=@@error
		end
		if @ErrVal!=0
		begin
			rollback tran p_UpdItem
			select @ItemId=0
			select @ErrVal=99999,@Msg="Failed to update new Item:" + @ItemCode
			goto ErrorHandler
		end
	commit tran p_UpdItem
    --end

	if @ErrVal!=0
	begin
			select @ErrVal=99999, @Msg="Failed to update ItemCode:" + @ItemCode
			goto ErrorHandler
	end
Done:

	return 0 

ErrorHandler:
	raiserror @ErrVal @Msg
end
go
EXEC sp_procxmode 'dbo.p_UpdItem','unchained'
go
IF OBJECT_ID('dbo.p_UpdItem') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_UpdItem >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_UpdItem >>>'
go
GRANT EXECUTE ON dbo.p_UpdItem TO public
go